﻿Imports Npgsql
Imports System.Text

Public Class clsTicket

    Private _ticketid As Integer
    Private _subject As String
    Private _subjectdesc As String
    Private _emailnotification As Boolean
    Private _issuetypeid As Integer
    Private _submitterid As Integer
    Private _assignedid As Integer
    Private _priorityid As Integer
    Private _prioritydesc As String
    Private _issuetypedesc As String
    Private _datecreated As DateTime
    Private _dateresolved As DateTime
    Private _submittedby As String
    Private _knowledgebase As Boolean

#Region "Properties"
    Public Property TicketID As Integer
        Get
            Return _ticketid
        End Get
        Set(ByVal value As Integer)
            _ticketid = value
        End Set
    End Property
    Public Property Subject As String
        Get
            Return _subject
        End Get
        Set(ByVal value As String)
            _subject = value
        End Set
    End Property
    Public Property SubjectDescription As String
        Get
            Return _subjectdesc
        End Get
        Set(ByVal value As String)
            _subjectdesc = value
        End Set
    End Property
    Public Property EmailNotification As Boolean
        Get
            Return _emailnotification
        End Get
        Set(ByVal value As Boolean)
            _emailnotification = value
        End Set
    End Property
    Public Property DateCreated As DateTime
        Get
            Return _datecreated
        End Get
        Set(ByVal value As DateTime)
            _datecreated = value
        End Set
    End Property
    Public Property DateResolved As DateTime
        Get
            Return _dateresolved
        End Get
        Set(ByVal value As DateTime)
            _dateresolved = value
        End Set
    End Property
    Public Property PriorityDesc As String
        Get
            Return _prioritydesc
        End Get
        Set(ByVal value As String)
            _prioritydesc = value
        End Set
    End Property
    Public Property IssueTypeDesc As String
        Get
            Return _issuetypedesc
        End Get
        Set(ByVal value As String)
            _issuetypedesc = value
        End Set
    End Property
    Public Property SubmittedBy As String
        Get
            Return _submittedby
        End Get
        Set(ByVal value As String)
            _submittedby = value
        End Set
    End Property
    Public Property AssignedId As Integer
        Get
            Return _assignedid
        End Get
        Set(ByVal value As Integer)
            _assignedid = value
        End Set
    End Property
    Public Property InKnowledgeBase As Boolean
        Get
            Return _knowledgebase
        End Get
        Set(ByVal value As Boolean)
            _knowledgebase = value
        End Set
    End Property
#End Region

#Region "Methods"
    Public Sub New()

    End Sub

    Public Sub New(ByVal id As Integer)
        _ticketid = id

        ' Populate class properties
        _retrieveTicketData()
    End Sub

    Public Function GetTicketNotes() As StringBuilder
        Dim sb As New StringBuilder
        Dim dsTicketNotes As DataSet

        ' Retrieve all notes from the ticketnotes relation tied to this ticket
        dsTicketNotes = _retrieveTicketNotes()

        ' Build a string with all of the notes from the ticketnotes relation tied to this ticket
        For Each row As DataRow In dsTicketNotes.Tables(0).Rows
            sb.AppendLine(String.Format("{0} --- {1}", row("datecreated"), row("note")))
            sb.AppendLine()
        Next

        Return sb
    End Function

    Public Sub AddNote(ByVal noteData As String)
        ' Add a note to the ticketnotes relation tied to this ticket
        _insertTicketNotes(noteData)
    End Sub

    Public Sub ResolveTicket()
        ' Close the ticket with the current system time
        _resolveTicket()

        ' Refersh the class properties from the ticket relation
        _retrieveTicketData()
    End Sub

    Public Sub AddTicketToKB()
        ' Add the ticket to the Knowledgebase
        _addToKB()
    End Sub

    Public Sub RemoveTicketFromKB()
        ' Remove the ticket from the Knowledgebase
        _removeFromKB()
    End Sub

    Public Sub UpdateAssignedTech(ByVal techid As Integer)
        ' Update the assignedid column in the ticket relation
        _updateAssignedTech(techid)
    End Sub

    Public Sub InsertTicket(ByVal submitterId As Integer, ByVal issuetypeId As Integer, ByVal priorityId As Integer, ByVal assignedId As Integer, ByVal Subject As String, ByVal SubjectDesc As String)
        ' Create the Data set
        Dim ds As New DataSet()

        Try
            ' Create Npgsql connection with connection string from UIBase
            Dim conn As New NpgsqlConnection(clsUIBase.DBConnString)

            ' Open the connection
            conn.Open()

            Dim sql As String = "INSERT INTO public.ticket (subject, subjectdesc, issuetypeid, submitterid, priorityid, assignedid) VALUES ('" & Subject & "','" & SubjectDesc & "','" & issuetypeId & "','" & submitterId & "','" & priorityId & "','" & assignedId & "')"

            ' Create and initialize the Npgsql data adapter
            Dim da As New NpgsqlCommand(sql, conn)
            da.ExecuteNonQuery()

            ' Close the connection
            conn.Close()
        Catch ex As NpgsqlException
            ' Catch/Display any npgsql exceptions
            MessageBox.Show("SQL exception: " & ex.Message)
        Catch ex As Exception
            ' Catch/Dispaly any remaining exceptions
            MessageBox.Show("There was an error: " & ex.Message)
        End Try
    End Sub
#End Region

#Region "Private Functions and Subs"
    Private Sub _retrieveTicketData()
        ' Attempt to connect to the DB to populate user properties based on username
        Try
            ' Create Npgsql connection with connection string from UIBase
            Dim conn As New NpgsqlConnection(clsUIBase.DBConnString)

            ' Open the connection
            conn.Open()

            ' Retrieve all columns from the users table pertaining to the current username from Login form
            Dim sql As String = "SELECT * FROM public.ticket " & _
                                "INNER JOIN public.issuetypedef ON ticket.issuetypeid = issuetypedef.issuetypeid " & _
                                "INNER JOIN public.ticketpriority ON ticket.priorityid = ticketpriority.priorityid " & _
                                "INNER JOIN public.users ON ticket.submitterid = users.userid " & _
                                "WHERE public.ticket.ticketid = " & _ticketid

            ' Create and initialize the Npgsql data adapter
            Dim da As New NpgsqlDataAdapter(sql, conn)

            ' Create and fill the Data set
            Dim ds As New DataSet()
            da.Fill(ds)

            ds.Tables(0).Columns.Add("submittedby", GetType(System.String), "lastname + ', ' + firstname + ' ( ID #' + userid + ' )'")

            If ds.Tables(0).Rows.Count = 1 Then
                ' Pull values from the Data Set and populate the properties
                _subject = ds.Tables(0).Rows(0).Item("subject")
                _subjectdesc = ds.Tables(0).Rows(0).Item("subjectdesc")
                _emailnotification = ds.Tables(0).Rows(0).Item("emailnotification")
                _issuetypeid = ds.Tables(0).Rows(0).Item("issuetypeid")
                _submitterid = ds.Tables(0).Rows(0).Item("submitterid")
                _priorityid = ds.Tables(0).Rows(0).Item("priorityid")
                _prioritydesc = ds.Tables(0).Rows(0).Item("prioritydesc")
                _issuetypedesc = ds.Tables(0).Rows(0).Item("issuetypedesc")
                _submittedby = ds.Tables(0).Rows(0).Item("submittedby")
                _datecreated = ds.Tables(0).Rows(0).Item("datecreated")
                _knowledgebase = ds.Tables(0).Rows(0).Item("knowledgebase")

                If IsDBNull(ds.Tables(0).Rows(0).Item("dateresolved")) Then
                    _dateresolved = Nothing
                Else
                    _dateresolved = ds.Tables(0).Rows(0).Item("dateresolved")
                End If

                If IsDBNull(ds.Tables(0).Rows(0).Item("assignedid")) Then
                    _assignedid = Nothing
                Else
                    _assignedid = ds.Tables(0).Rows(0).Item("assignedid")
                End If
            End If

            ' Close the connection
            conn.Close()
        Catch ex As NpgsqlException
            ' Catch/Display any npgsql exceptions
            MessageBox.Show("SQL exception: " & ex.Message)
        Catch ex As Exception
            ' Catch/Dispaly any remaining exceptions
            MessageBox.Show("There was an error: " & ex.Message)
        End Try
    End Sub

    Private Function _retrieveTicketNotes() As DataSet
        ' Create the Data set
        Dim ds As New DataSet()

        Try
            ' Create Npgsql connection with connection string from UIBase
            Dim conn As New NpgsqlConnection(clsUIBase.DBConnString)

            ' Open the connection
            conn.Open()

            Dim sql As String = "SELECT * FROM public.ticketnotes " & _
                                "WHERE public.ticketnotes.ticketid = " & _ticketid & _
                                " ORDER BY public.ticketnotes.datecreated DESC"

            ' Create and initialize the Npgsql data adapter
            Dim da As New NpgsqlDataAdapter(sql, conn)

            da.Fill(ds)

            ' Close the connection
            conn.Close()
        Catch ex As NpgsqlException
            ' Catch/Display any npgsql exceptions
            MessageBox.Show("SQL exception: " & ex.Message)
        Catch ex As Exception
            ' Catch/Dispaly any remaining exceptions
            MessageBox.Show("There was an error: " & ex.Message)
        End Try

        Return ds
    End Function

    Private Sub _insertTicketNotes(ByVal data As String)
        Try
            ' Create Npgsql connection with connection string from UIBase
            Dim conn As New NpgsqlConnection(clsUIBase.DBConnString)

            ' Open the connection
            conn.Open()

            Dim sql As String = "INSERT INTO public.ticketnotes (note, ticketid) VALUES ('" & data & "','" & _ticketid & "')"

            ' Create and initialize the Npgsql data adapter
            Dim da As New NpgsqlCommand(sql, conn)
            da.ExecuteNonQuery()

            ' Close the connection
            conn.Close()
        Catch ex As NpgsqlException
            ' Catch/Display any npgsql exceptions
            MessageBox.Show("SQL exception: " & ex.Message)
        Catch ex As Exception
            ' Catch/Dispaly any remaining exceptions
            MessageBox.Show("There was an error: " & ex.Message)
        End Try
    End Sub

    Private Sub _resolveTicket()
        Try
            ' Create the Npgsql connection with the conection string from UIBase
            Dim conn As New NpgsqlConnection(clsUIBase.DBConnString)

            ' Open the connection
            conn.Open()

            Dim sql As String = "UPDATE public.ticket SET dateresolved = '" & System.DateTime.Now & "' WHERE public.ticket.ticketid = " & _ticketid

            ' Create and initialize the Npgsql data adapter
            Dim da As New NpgsqlCommand(sql, conn)
            da.ExecuteNonQuery()

            ' Close the connection
            conn.Close()
        Catch ex As NpgsqlException
            ' Catch/Display any npgsql exceptions
            MessageBox.Show("SQL exception: " & ex.Message)
        Catch ex As Exception
            ' Catch/Dispaly any remaining exceptions
            MessageBox.Show("There was an error: " & ex.Message)
        End Try
    End Sub

    Private Sub _addToKB()
        Try
            ' Create the Npgsql connection with the conection string from UIBase
            Dim conn As New NpgsqlConnection(clsUIBase.DBConnString)

            ' Open the connection
            conn.Open()

            Dim sql As String = "UPDATE public.ticket SET knowledgebase = TRUE WHERE public.ticket.ticketid = " & _ticketid

            ' Create and initialize the Npgsql data adapter
            Dim da As New NpgsqlCommand(sql, conn)
            da.ExecuteNonQuery()

            ' Close the connection
            conn.Close()
        Catch ex As NpgsqlException
            ' Catch/Display any npgsql exceptions
            MessageBox.Show("SQL exception: " & ex.Message)
        Catch ex As Exception
            ' Catch/Dispaly any remaining exceptions
            MessageBox.Show("There was an error: " & ex.Message)
        End Try
    End Sub

    Private Sub _removeFromKB()
        Try
            ' Create the Npgsql connection with the conection string from UIBase
            Dim conn As New NpgsqlConnection(clsUIBase.DBConnString)

            ' Open the connection
            conn.Open()

            Dim sql As String = "UPDATE public.ticket SET knowledgebase = FALSE WHERE public.ticket.ticketid = " & _ticketid

            ' Create and initialize the Npgsql data adapter
            Dim da As New NpgsqlCommand(sql, conn)
            da.ExecuteNonQuery()

            ' Close the connection
            conn.Close()
        Catch ex As NpgsqlException
            ' Catch/Display any npgsql exceptions
            MessageBox.Show("SQL exception: " & ex.Message)
        Catch ex As Exception
            ' Catch/Dispaly any remaining exceptions
            MessageBox.Show("There was an error: " & ex.Message)
        End Try
    End Sub

    Private Sub _updateAssignedTech(ByVal techid As Integer)
        Try
            ' Create the Npgsql connection with the connection string from UIBase
            Dim conn As New NpgsqlConnection(clsUIBase.DBConnString)

            ' Open the connection
            conn.Open()

            Dim sql As String = "UPDATE public.ticket SET assignedid = " & techid & " WHERE public.ticket.ticketid = " & _ticketid

            ' Create and initialize the Npgsql data adapter
            Dim da As New NpgsqlCommand(sql, conn)
            da.ExecuteNonQuery()

            ' Close the connection
            conn.Close()
        Catch ex As NpgsqlException
            ' Catch/Display any npgsql exceptions
            MessageBox.Show("SQL exception: " & ex.Message)
        Catch ex As Exception
            ' Catch/Display any remaing exceptions
            MessageBox.Show("There was an error: " & ex.Message)
        End Try
    End Sub
#End Region
End Class
